-- MindMate AI Chat System Database Schema
-- 初始化脚本，用于创建Supabase数据库表结构
-- 支持多用户架构，预留用户认证功能

-- 创建用户表 (预留，目前系统为单用户)
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE,
    username TEXT UNIQUE,
    display_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建会话表
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID DEFAULT '00000000-0000-0000-0000-000000000000'::uuid, -- 默认用户ID（单用户模式）
    title TEXT NOT NULL,
    role_id TEXT, -- 角色ID (预设角色用字符串，自定义角色用UUID)
    model TEXT NOT NULL DEFAULT 'deepseek-v3-1-250821',
    search_enabled BOOLEAN DEFAULT false,
    memo TEXT, -- 对话摘要
    memo_enabled BOOLEAN DEFAULT false,
    is_favorite BOOLEAN DEFAULT false,
    is_archived BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建消息表
CREATE TABLE IF NOT EXISTS messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content TEXT NOT NULL,
    model TEXT, -- 生成该消息的模型（仅assistant消息需要）
    created_at TIMESTAMPTZ DEFAULT NOW(),
    message_index INTEGER NOT NULL -- 消息在会话中的顺序
);

-- 创建自定义角色表
CREATE TABLE IF NOT EXISTS custom_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID DEFAULT '00000000-0000-0000-0000-000000000000'::uuid, -- 默认用户ID（单用户模式）
    name TEXT NOT NULL,
    title TEXT, -- 职业/身份
    input_spec TEXT NOT NULL,
    output_spec TEXT NOT NULL,
    custom_traits JSONB DEFAULT '[]', -- 角色特征数组 [{"key": "背景", "value": "10年经验"}]
    system_prompt_snapshot TEXT NOT NULL,
    usage_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建索引以优化查询性能
-- 用户表索引
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at DESC);

-- 会话表索引 (包含user_id用于多用户查询)
CREATE INDEX IF NOT EXISTS idx_sessions_user_created ON sessions(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_user_updated ON sessions(user_id, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_user_archived ON sessions(user_id, is_archived, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_user_favorite ON sessions(user_id, is_favorite, updated_at DESC);

-- 消息表索引
CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id, message_index);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);
CREATE INDEX IF NOT EXISTS idx_messages_role ON messages(role);

-- 自定义角色表索引 (包含user_id用于多用户查询)
CREATE INDEX IF NOT EXISTS idx_custom_roles_user_created ON custom_roles(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_custom_roles_user_usage ON custom_roles(user_id, usage_count DESC, last_used_at DESC);

-- 创建更新时间戳的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language plpgsql;

-- 为会话表添加自动更新时间戳触发器
DROP TRIGGER IF EXISTS update_sessions_updated_at ON sessions;
CREATE TRIGGER update_sessions_updated_at
    BEFORE UPDATE ON sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 为自定义角色表添加自动更新时间戳触发器
DROP TRIGGER IF EXISTS update_custom_roles_updated_at ON custom_roles;
CREATE TRIGGER update_custom_roles_updated_at
    BEFORE UPDATE ON custom_roles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 为用户表添加自动更新时间戳触发器
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 启用行级安全策略 (Row Level Security) - 为多用户准备
-- 当前为单用户模式，所以使用默认用户ID，但为未来多用户做准备

-- 用户表RLS策略
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- 会话表RLS策略 - 用户只能访问自己的会话
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "用户只能查看自己的会话" ON sessions
    FOR SELECT USING (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());
CREATE POLICY "用户只能创建自己的会话" ON sessions
    FOR INSERT WITH CHECK (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());
CREATE POLICY "用户只能更新自己的会话" ON sessions
    FOR UPDATE USING (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());
CREATE POLICY "用户只能删除自己的会话" ON sessions
    FOR DELETE USING (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());

-- 消息表RLS策略 - 通过会话关联控制访问权限
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY "用户只能查看自己会话的消息" ON messages
    FOR SELECT USING (
        session_id IN (
            SELECT id FROM sessions 
            WHERE user_id = '00000000-0000-0000-0000-000000000000'::uuid 
            OR user_id = auth.uid()
        )
    );
CREATE POLICY "用户只能在自己的会话中创建消息" ON messages
    FOR INSERT WITH CHECK (
        session_id IN (
            SELECT id FROM sessions 
            WHERE user_id = '00000000-0000-0000-0000-000000000000'::uuid 
            OR user_id = auth.uid()
        )
    );
CREATE POLICY "用户只能更新自己会话的消息" ON messages
    FOR UPDATE USING (
        session_id IN (
            SELECT id FROM sessions 
            WHERE user_id = '00000000-0000-0000-0000-000000000000'::uuid 
            OR user_id = auth.uid()
        )
    );
CREATE POLICY "用户只能删除自己会话的消息" ON messages
    FOR DELETE USING (
        session_id IN (
            SELECT id FROM sessions 
            WHERE user_id = '00000000-0000-0000-0000-000000000000'::uuid 
            OR user_id = auth.uid()
        )
    );

-- 自定义角色表RLS策略 - 用户只能访问自己的角色
ALTER TABLE custom_roles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "用户只能查看自己的角色" ON custom_roles
    FOR SELECT USING (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());
CREATE POLICY "用户只能创建自己的角色" ON custom_roles
    FOR INSERT WITH CHECK (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());
CREATE POLICY "用户只能更新自己的角色" ON custom_roles
    FOR UPDATE USING (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());
CREATE POLICY "用户只能删除自己的角色" ON custom_roles
    FOR DELETE USING (user_id = '00000000-0000-0000-0000-000000000000'::uuid OR user_id = auth.uid());

-- 创建默认用户 (单用户模式)
INSERT INTO users (id, display_name, username)
VALUES ('00000000-0000-0000-0000-000000000000', 'Default User', 'default')
ON CONFLICT (id) DO UPDATE SET
    display_name = EXCLUDED.display_name,
    username = EXCLUDED.username;

-- 插入一些示例数据（可选，用于测试）
-- 注意：在生产环境中可以移除这部分
INSERT INTO sessions (id, user_id, title, role_id, model) 
VALUES 
    ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000000', '示例对话', 'emma', 'deepseek-v3-1-250821')
ON CONFLICT (id) DO NOTHING;

INSERT INTO messages (session_id, role, content, message_index)
VALUES 
    ('00000000-0000-0000-0000-000000000001', 'user', '你好！', 1),
    ('00000000-0000-0000-0000-000000000001', 'assistant', '你好！我是Emma，很高兴认识你。有什么我可以帮助你的吗？', 2)
ON CONFLICT (id) DO NOTHING;

-- 完成提示
SELECT 'Database schema initialized successfully!' as status;